﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.OleDb;

namespace DAO
{
    public class DoanhThuDAO
    {
        public List<DoanhThuDTO> Laysohoadontheochinh()
        {
            OleDbConnection conn = DataProvider.ConnectDB();
            string sql = "SELECT MaHD,NgayHD,TenKH,ThanhTien,GhiChu FROM HoaDon";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<DoanhThuDTO> danhsach = new List<DoanhThuDTO>();
            while(reader.Read())
            {
                DoanhThuDTO dtDTO = new DoanhThuDTO();
                dtDTO.MaHD=reader.GetInt32(0);
                dtDTO.NgayHD = reader.GetDateTime(1);
                dtDTO.TenKH=reader.GetString(2);
                dtDTO.ThanhTien=reader.GetInt32(3);
                dtDTO.GhiChu=reader.GetString(4);
                danhsach.Add(dtDTO);
            }
              conn.Close();
            return danhsach;
        }
        public double TongTienHDTheoNgay(DateTime st,DateTime en)
        {
            string sql = "select sum(ThanhTien) from HoaDon where NgayHD between #{0}# and #{1}#";
            sql = string.Format(sql, st.ToShortDateString(),en.ToShortDateString());
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            object obj = cmd.ExecuteScalar();
            double kq = obj == DBNull.Value ? 0 : Convert.ToDouble(obj);
            conn.Close();
            return kq;
        }
        public OleDbDataAdapter BCDoanhThu(DateTime batdau, DateTime ketthuc)
        {
            string sql = "select * from HoaDon where NgayHD between " + "#" + batdau.ToShortDateString() + "#" + " and " + "#" + ketthuc.ToShortDateString() + "# ";
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            conn.Close();
            return da;
        }
    }
}
